﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;
using Model;

namespace DAL
{
    public class ParticipantsDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录 
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoParticipants model)
        {
            model.PAR_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Participants]");
            sql.Append("([PAR_ID],[PAR_STUDENTS],[PAR_GROUPS],[PAR_CONTESTS],[PAR_AWARDS],[PAR_SPECIALTY],[PAR_SCORE],[PAR_IsShow],[PAR_SUMURL],[PAR_SUMMARY],[PAR_SUMTIME],[PAR_SUMTITLE])");
            sql.Append("VALUES");
            sql.Append("(@PAR_ID,@PAR_STUDENTS,@PAR_GROUPS,@PAR_CONTESTS,@PAR_AWARDS,@PAR_SPECIALTY,@PAR_SCORE,@PAR_IsShow,@PAR_SUMURL,@PAR_SUMMARY,@PAR_SUMTIME,@PAR_SUMTITLE)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoParticipants model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Participants] SET ");
            sql.Append("PAR_STUDENTS=@PAR_STUDENTS");
            sql.Append(",PAR_GROUPS=@PAR_GROUPS");
            sql.Append(",PAR_CONTESTS=@PAR_CONTESTS");
            sql.Append(",PAR_AWARDS=@PAR_AWARDS");
            sql.Append(",PAR_SPECIALTY=@PAR_SPECIALTY");
            sql.Append(",PAR_SCORE=@PAR_SCORE");
            sql.Append(",PAR_IsShow=@PAR_IsShow");
            sql.Append(",PAR_SUMMARY=@PAR_SUMMARY");
            sql.Append(",PAR_SUMURL=@PAR_SUMURL");
            sql.Append(",PAR_SUMTIME=@PAR_SUMTIME");
            sql.Append(",PAR_SUMTITLE=@PAR_SUMTITLE");
            sql.Append(" WHERE ");
            sql.Append("PAR_ID=@PAR_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }
        /// <summary>
        /// 检测学生是否已经参加此竞赛
        /// </summary>
        /// <param name="participants"></param>
        /// <returns></returns>
        public int CheckStuId(BoParticipants model)
        {
            int result =0;
            string sql = "select count(PAR_ID) from Participants where PAR_CONTESTS= @PAR_CONTESTS and PAR_STUDENTS = @PAR_STUDENTS";
            DataTable table = dbhelper.ExcuteQuery(sql, GetParameters(model));
            if (table!=null&&int.TryParse(table.Rows[0][0].ToString(), out result))
            {
            }
            return result;
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string pAR_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Participants");
            sql.Append(" WHERE ");
            sql.Append("PAR_ID=@PAR_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pAR_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        /// <summary>
        /// 根据学生id来删除相应的数据
        /// </summary>
        /// <param name="STU_ID">学生id</param>
        /// <returns></returns>
        public int UpdateSummary(BoParticipants model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Participants] SET ");
            sql.Append("PAR_SUMMARY=@PAR_SUMMARY");
            sql.Append(",PAR_SUMURL=@PAR_SUMURL");
            sql.Append(",PAR_SUMTIME=@PAR_SUMTIME");
            sql.Append(",PAR_SUMTITLE=@PAR_SUMTITLE");
            
            sql.Append(" WHERE ");
            sql.Append("PAR_ID=@PAR_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        
        }



        public int DeleteByStuId(string STU_ID, string PAR_CONTESTS)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM Participants where PAR_STUDENTS = @PAR_STUDENTS and PAR_CONTESTS = @PAR_CONTESTS");
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@PAR_STUDENTS",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_CONTESTS",SqlDbType.NVarChar,50)
            };
            parameter[0].Value = STU_ID;
            parameter[1].Value = PAR_CONTESTS;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        public int RemoveGroupParticipants(string groupId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Participants");
            sql.Append(" WHERE ");
            sql.Append("PAR_GROUPS=@PAR_GROUPS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_GROUPS", SqlDbType.NVarChar, 50);
            parameter[0].Value = groupId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoParticipants GetModel(string pAR_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SCORE,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTITLE,PAR_SUMTIME,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("PAR_ID=@PAR_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pAR_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public BoParticipants GetModelByCode(string code, string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_STUDENTS,PAR_SCORE,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("STU_CODE=@STU_CODE AND PAR_CONTESTS=@PAR_CONTESTS");
            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@STU_CODE", SqlDbType.NVarChar, 50);
            parameter[0].Value = code;
            parameter[1] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[1].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoParticipants> GetAllParticipants()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_STUDENTS,PAR_SCORE,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoParticipants> list = GetList(table);
            return list;

        }
        public IList<BoParticipants> GetAwardsPar( string contestId )
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE");
            sql.Append(" PAR_AWARDS != @PAR_AWARDS AND PAR_CONTESTS=@PAR_CONTESTS");

            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@PAR_AWARDS", SqlDbType.NVarChar, 50);
            parameter[0].Value = "-1";
            parameter[1] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[1].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;

           
        }
        public IList<BoParticipants> GetAllOutterAwardsPar()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN (Contests AS CON LEFT JOIN Competitions AS COM ON CON.CON_COMPETITIONS=COM.COM_ID) ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE");
            sql.Append(" PAR_AWARDS != @PAR_AWARDS AND COM.COM_TYPE=1");

            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_AWARDS", SqlDbType.NVarChar, 50);
            parameter[0].Value = "-1";
           
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }

        public IList<BoParticipants> GetAllInnerAwardsPar()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN (Contests AS CON LEFT JOIN Competitions AS COM ON CON.CON_COMPETITIONS=COM.COM_ID) ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE");
            sql.Append(" PAR_AWARDS != @PAR_AWARDS AND COM.COM_TYPE=0");

            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_AWARDS", SqlDbType.NVarChar, 50);
            parameter[0].Value = "-1";

            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }

        public IList<BoParticipants> GetContestAwards(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append(" SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME ");
            sql.Append(" FROM Participants AS P LEFT JOIN Students AS Stu ON P.PAR_STUDENTS=Stu.STU_ID ");
            sql.Append(" LEFT JOIN Classes AS Cla ON Stu.STU_CLASSES=Cla.CLA_ID ");
            sql.Append(" LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ");
            sql.Append(" LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID ");
            sql.Append(" LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID ");
            sql.Append(" LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID ");
            sql.Append(" WHERE");
            sql.Append(" PAR_CONTESTS = @PAR_CONTESTS AND PAR_AWARDS !='-1'");

            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;


        }

        public IList<BoParticipants> GetContestParticipants(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("PAR_CONTESTS=@PAR_CONTESTS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 根据竞赛获取已经有成绩的报名信息
        /// </summary>
        /// <param name="contestId">竞赛id</param>
        /// <returns>返回相应的报名信息</returns>
        public IList<BoParticipants> GetContestHasScoreParticipants(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("PAR_CONTESTS=@PAR_CONTESTS and PAR_SCORE is not null ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 根据竞赛id来获取没有分数报名信息
        /// </summary>
        /// <param name="contestId">竞赛id</param>
        /// <returns>返回相应的对象表</returns>
        public IList<BoParticipants> GetContestHasNoScoreParticipants(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("PAR_CONTESTS=@PAR_CONTESTS and PAR_SCORE is null");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }

        public IList<BoParticipants> GetSummaryParticipantsByContest(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE ");
            sql.Append("PAR_CONTESTS=@PAR_CONTESTS and PAR_IsShow=1 and ((PAR_SUMMARY not like '' AND PAR_SUMMARY is not null) OR (PAR_SUMURL not like '' AND PAR_SUMURL is not null) ) ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }
        
        public IList<BoParticipants> GetGroupParticipants(string groupId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PAR_ID,PAR_SCORE,PAR_STUDENTS,PAR_GROUPS,PAR_CONTESTS,PAR_AWARDS,PAR_SPECIALTY,PAR_IsShow,PAR_SUMURL,PAR_SUMMARY,PAR_SUMTIME,PAR_SUMTITLE,STU_CODE,STU_NAME,CLA_NAME,ACA_NAME,CON_NAME,CON_ID,GRO_NAME,AWA_NAME FROM Participants AS P LEFT JOIN (Students AS Stu LEFT JOIN (Classes AS Cla LEFT JOIN Academy AS ACA ON Cla.CLA_ACADEMY=ACA.ACA_ID ) ON Stu.STU_CLASSES=Cla.CLA_ID )ON P.PAR_STUDENTS=Stu.STU_ID LEFT JOIN Contests AS CON ON P.PAR_CONTESTS=CON.CON_ID LEFT JOIN Groups AS GRO ON P.PAR_GROUPS=GRO.GRO_ID LEFT JOIN Awards AS AWA ON P.PAR_AWARDS=AWA.AWA_ID");
            sql.Append(" WHERE GRO_IsShow=1 and ");
            sql.Append("PAR_GROUPS=@PAR_GROUPS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_GROUPS", SqlDbType.NVarChar, 50);
            parameter[0].Value = groupId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoParticipants> list = GetList(table);
            return list;
        }
        /// <summary>
        /// 获得V_Participant的视图
        /// </summary>
        /// <param name="constr"></param>
        /// <returns></returns>
        public DataTable GetV_Participant(string constr) { 
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from v_Participant where PAR_IsShow =1 ");
            if (!string.IsNullOrEmpty(constr))
            {
                sql.Append("and ");
                sql.Append(constr);
            }
            return dbhelper.ExcuteQuery(sql.ToString());
        }

         /// <summary>
        /// 根据竞赛的id来获取V_Participant的视图
        /// </summary>
        /// <param name="ConId">竞赛id</param>
        /// <returns>返回table</returns>
        public DataTable GetV_ParticipantByConId(string ConId)
        {
            string constr = "CON_ID='"+ConId+"'";
            return GetV_Participant(constr);
        }

        private List<BoParticipants> GetList(DataTable table)
        {
            List<BoParticipants> list = new List<BoParticipants>();
            if (table != null && table.Rows.Count > 0)
            {
                
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoParticipants model = new BoParticipants();
                    model.PAR_ID = (string)table.Rows[i]["PAR_ID"];
                    model.PAR_STUDENTS = table.Rows[i]["PAR_STUDENTS"] == DBNull.Value ? "" : (string)table.Rows[i]["PAR_STUDENTS"];
                    model.PAR_GROUPS = table.Rows[i]["PAR_GROUPS"] == DBNull.Value ? "" : (string)table.Rows[i]["PAR_GROUPS"];
                    model.PAR_CONTESTS = table.Rows[i]["PAR_CONTESTS"]==DBNull.Value?"" : (string)table.Rows[i]["PAR_CONTESTS"];
                    model.PAR_AWARDS = table.Rows[i]["PAR_AWARDS"] == DBNull.Value ? "" : (string)table.Rows[i]["PAR_AWARDS"];
                    model.PAR_SPECIALTY = (Int32)table.Rows[i]["PAR_SPECIALTY"];
                    model.PAR_IsShow = (int)table.Rows[i]["PAR_IsShow"];
                    model.ACA_NAME = table.Rows[i]["ACA_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["ACA_NAME"];
                    model.CLA_NAME = table.Rows[i]["CLA_NAME"]== DBNull.Value ? "" :(string)table.Rows[i]["CLA_NAME"];
                    model.CON_NAME = table.Rows[i]["CON_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["CON_NAME"];
                    model.GRO_NAME = table.Rows[i]["GRO_NAME"]==DBNull.Value?"":(string)table.Rows[i]["GRO_NAME"];
                    model.STU_CODE = table.Rows[i]["STU_CODE"] == DBNull.Value ? "" : (string)table.Rows[i]["STU_CODE"];
                    model.STU_NAME = table.Rows[i]["STU_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["STU_NAME"];
                    model.AWA_NAME=table.Rows[i]["AWA_NAME"]==DBNull.Value?"":(string)table.Rows[i]["AWA_NAME"];
                    model.CON_ID = table.Rows[i]["CON_ID"] == DBNull.Value ? "" : (string)table.Rows[i]["CON_ID"];
                    model.PAR_SCORE = table.Rows[i]["PAR_SCORE"]==DBNull.Value?0:(int)table.Rows[i]["PAR_SCORE"];
                    model.PAR_SUMURL=table.Rows[i]["PAR_SUMURL"]==DBNull.Value? "":(string)table.Rows[i]["PAR_SUMURL"];
                    model.PAR_SUMMARY = table.Rows[i]["PAR_SUMMARY"] == DBNull.Value ? "" : (string)table.Rows[i]["PAR_SUMMARY"];
                    model.PAR_SUMTITLE=table.Rows[i]["PAR_SUMTITLE"]==DBNull.Value?"":(string)table.Rows[i]["PAR_SUMTITLE"];
                    model.PAR_SUMTIME = table.Rows[i]["PAR_SUMTIME"] == System.DBNull.Value ? DateTime.Now : (DateTime)table.Rows[i]["PAR_SUMTIME"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoParticipants model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@PAR_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_STUDENTS",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_GROUPS",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_CONTESTS",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_AWARDS",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_SPECIALTY",SqlDbType.Int,4),
                new SqlParameter("@PAR_IsShow",SqlDbType.Int,2),
                new SqlParameter("@STU_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@CLA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@ACA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@GRO_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@AWA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_SCORE",SqlDbType.Decimal),
                new SqlParameter("@PAR_SUMURL",SqlDbType.NVarChar,100),
                new SqlParameter("@PAR_SUMMARY",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_SUMTITLE",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_SUMTIME",SqlDbType.Date,50)
            };
            parameters[0].Value = model.PAR_ID;
            parameters[1].Value = model.PAR_STUDENTS;
            parameters[2].Value = model.PAR_GROUPS;
            parameters[3].Value = model.PAR_CONTESTS;
            parameters[4].Value = model.PAR_AWARDS;
            parameters[5].Value = model.PAR_SPECIALTY;
            parameters[6].Value = model.PAR_IsShow;
            parameters[7].Value = model.STU_NAME;
            parameters[8].Value = model.STU_CODE;
            parameters[9].Value = model.CLA_NAME;
            parameters[10].Value = model.ACA_NAME;
            parameters[11].Value = model.GRO_NAME;
            parameters[12].Value = model.CON_NAME;
            parameters[13].Value = model.AWA_NAME;
            parameters[14].Value =model.CON_ID;
            parameters[15].Value = model.PAR_SCORE;
            parameters[16].Value = model.PAR_SUMURL;
            parameters[17].Value = model.PAR_SUMMARY;           
            parameters[18].Value = model.PAR_SUMTITLE;
            parameters[19].Value = model.PAR_SUMTIME;

            return parameters;
        }
         /// <summary>
        /// 根据竞赛id以及学生学号获取报名列表和学生的视图
        /// </summary>
        /// <param name="contestId">竞赛id</param>
        /// <param name="studentCode">学生学号</param>
        /// <returns>返回相应的表格数据</returns>
        public DataTable GetParicipantAndStudentByCode(string studentCode, string contestId)
        {
            string sql = "select * from v_participantAndStu where PAR_IsShow = 1 and PAR_CONTESTS like @PAR_CONTESTS and STU_CODE like @STU_CODE";
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@PAR_CONTESTS",contestId),
                new SqlParameter("@STU_CODE",studentCode)
            };
            return dbhelper.ExcuteQuery(sql, parameter);
        }
         /// <summary>
        /// 根据小组的id获取报名列表和学生的视图
        /// </summary>
        /// <param name="GroupId">小组id</param>
        /// <returns>返回相应的表格数据</returns>
        public DataTable GetParicipantAndStudentByGrup(string GroupId)
        {
            string sql = "select * from v_participantAndStu where PAR_GROUPS=@PAR_CONTESTS ";
            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@PAR_CONTESTS",GroupId),
            };
            return dbhelper.ExcuteQuery(sql, parameter);
        }


        /// <summary>
        /// 更新参赛列表下的获奖字段（个人报名）
        /// </summary>
        /// <param name="Con_Id">竞赛id</param>
        /// <param name="PercentList">百分比对象列表</param>
        /// <returns>返回修改的行数</returns>
        public int innerAward(string Con_Id, IList<BoPercent> PercentList)
        {
            int result = 0;
            int allCount = GetPercentCount(Con_Id);
            foreach (BoPercent percentmodel in PercentList)
            {
                float Ftopnumber = (float)allCount * ((float)percentmodel.PER_PERSENT)/100;
                int Itopnumber = (int)Ftopnumber;
                int topnumber = Ftopnumber > (float)Itopnumber ? Itopnumber + 1 : Itopnumber;
                string sql = "select top " + topnumber + " PAR_ID from Participants where PAR_AWARDS = '-1' and PAR_CONTESTS='" + Con_Id + "' order by PAR_SCORE desc";
                DataTable table = dbhelper.ExcuteQuery(sql);
                string idList = GetIdList(table);
                string excutesql = "update Participants set PAR_AWARDS = '" + percentmodel.PER_AWARDS + "' where PAR_ID in ("+idList+")";
                result += dbhelper.ExcuteNoQuery(excutesql);
            }
            return result;
        }

        /// <summary>
        /// 更新参赛列表下的获奖字段（小组报名）
        /// </summary>
        /// <param name="Con_Id">竞赛id</param>
        /// <param name="PercentList">百分比列表</param>
        /// <returns>返回修改的行数</returns>
        public int innerGroupAward(string Con_Id, IList<BoPercent> PercentList)
        {
            int result = 0;
            int allCount = GetGroupCount(Con_Id);
            foreach (BoPercent percentmodel in PercentList)
            {
                float Ftopnumber = (float)allCount * ((float)percentmodel.PER_PERSENT) / 100;
                int Itopnumber = (int)Ftopnumber;
                int topnumber = Ftopnumber > (float)Itopnumber ? Itopnumber + 1 : Itopnumber;
                string sql = "select  top " + topnumber + " GRO_ID,AVG(PAR_SCORE) from v_participantStuAndGroup where PAR_AWARDS = '-1' and PAR_CONTESTS='" + Con_Id + "' group by GRO_ID order by avg(PAR_SCORE) desc";
                DataTable table = dbhelper.ExcuteQuery(sql);
                string idList = GetIdList(table);
                string excutesql = "update Participants set PAR_AWARDS = '" + percentmodel.PER_AWARDS + "' where PAR_GROUPS in (" + idList + ")";
                result += dbhelper.ExcuteNoQuery(excutesql);
            }
            return result;
        }

        /// <summary>
        /// 将table下的内容转换成字符串，用，分开
        /// </summary>
        /// <param name="table">表格</param>
        /// <returns>返回字符串</returns>
        public string GetIdList(DataTable table)
        {
            StringBuilder result = new StringBuilder() ;
            if (table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count - 1; i++)
                {
                    result.Append("'");
                    result.Append(table.Rows[i][0]);
                    result.Append("',");
                }
                result.Append("'");
                result.Append(table.Rows[table.Rows.Count - 1][0]);
                result.Append("'");
            }
            else
            {
                result.Append("''");
            }
            return result.ToString();
        }

        /// <summary>
        /// 得到没有获得奖项的总的个数
        /// </summary>
        /// <param name="Con_Id"></param>
        /// <returns></returns>
        public int GetPercentCount(string Con_Id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(*) from Participants where PAR_CONTESTS = '" + Con_Id + "' and PAR_IsShow=1 ");
            return (int)dbhelper.ExcuteQuery(sql.ToString()).Rows[0][0];
        }

        /// <summary>
        /// 得到没有获奖的小组的总的个数
        /// </summary>
        /// <param name="Con_Id"></param>
        /// <returns></returns>
        public int GetGroupCount(string Con_Id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(*) from Groups where GRO_CONTESTS = '" + Con_Id + "'");
            return (int)dbhelper.ExcuteQuery(sql.ToString()).Rows[0][0];
        }

        /// <summary>
        /// 根据竞赛的id将该竞赛下的所欲的PAR_AWARDS置为-1
        /// </summary>
        /// <param name="Con_Id"></param>
        public void ReSetPAR_AWARDS(string Con_Id)
        {
            String sql = "update Participants set PAR_AWARDS = -1 where PAR_CONTESTS =@PAR_CONTESTS";
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@PAR_CONTESTS",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = Con_Id;
            dbhelper.ExcuteNoQuery(sql,parameter);
        }

          /// <summary>
        /// 根据竞赛id来检测是否是小组
        /// </summary>
        /// <param name="Con_Id"></param>
        /// <returns>返回查询到的结果</returns>
        public int chekIsGroup(string Con_Id)
        {
            string sql = "select count(*) from Participants where PAR_GROUPS is not null and PAR_CONTESTS = @PAR_CONTESTS  ";
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@PAR_CONTESTS",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = Con_Id;
            return (int)dbhelper.ExcuteQuery(sql,parameter).Rows[0][0];

        }

         /// <summary>
        /// 更新具有小组的成绩
        /// </summary>
        /// <param name="ConId">竞赛id</param>
        /// <param name="Score">成绩</param>
        /// <param name="GroupId">小组id</param>
        /// <returns>返回受影响的行数</returns>
        public int updateGroupScore(string ConId,int Score, string GroupId)
        {
            string sql = "update Participants set PAR_SCORE=@PAR_SCORE where PAR_GROUPS=@PAR_GROUPS and PAR_CONTESTS = @PAR_CONTESTS and PAR_IsShow=1 ";
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@PAR_SCORE",SqlDbType.Int),
                new SqlParameter("@PAR_GROUPS",SqlDbType.NVarChar,50),
                new SqlParameter("@PAR_CONTESTS",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = Score;
            parameter[1].Value = GroupId;
            parameter[2].Value = ConId;
            return dbhelper.ExcuteNoQuery(sql,parameter);
        }
    }
}